USING FORMULAS

Formula is used for calculating various calculations and returns a result, which is displayed in the cell. In formula there are variety of operators and built functions to work with values and text. The values and text used in formulas can be located in other cells, which makes changing data easy and gives worksheets their dynamic nature.

Elements of Formulas

  1. Mathematical operators, such as (+,-,*,/).

      2. Example:
    • =6+5 Ans=11 =6*5 Ans=30
    • =6-5 Ans=1 =6/5 Ans=1.2

  2. Cell references (including named cells and ranges)



      3. Example:
    • =A2+B2 In the above example A2 and B2 are cell references for the value 5 and 6 respectively.

  3. Built in Function



      4. Example:
    • =SUM(A2:B2) Here SUM is the built in function for addition of two numbers 5 and 6.

    Rules for Formulas


    • A formula is a special entry in a cell, that calculates its value based on other cells, and/or constants
    • Formula is written in the cell where your result will display.
    • Formula is begin with “=” sign and without the equal sign “=”Microsoft Excel assumes you’ve entered a label or value.
    • After you enter formula press enter key to get results.

    CELL REFERENCES IN FORMULAS

    Most formulas you create include references to cells or ranges. These references enable your formulas to work dynamically with the data contained in those cells or ranges. For example, if your formula refers to cell A2 and you change the value contained in A2, the formula result reflects new value automatically. If you didn’t use references in your formulas, you would need to edit the formulas themselves in order to change the values used in the formulas.
    Cell reference in a formula can be used in three types: relative, absolute, and mixed references.


    RELATIVE CELL REFERENCES

    The row and column references can change when you copy the formula to another cell because the references are actually offsets from the current row and column. By default, Excel creates relative cell references in formulas.




    ABSOLUTE CELL REFERENCES

    The row and column references do not change when you copy the formula because the reference is to an actual cell address. An absolute reference uses two dollar signs in its address: one for the column letter and one for the row number (for example, $A$1).





    MIXED CELL REFERENCES

    Either row or column reference is relative or, the other is absolute. Only one part on an address is relative (for example, =SUM($B4:$M4)).